![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Design ConsiderationsBy looking at the data access patterns for your system, you should have a good idea of how the system operates. Before looking at the design of the system, here is a review of a few concepts introduced in earlier chapters:
All these factors contribute to the optimal data layout of the system. The physical layoutalong with SGA and shared pool tuningcreates an optimally configured server for decision support tasks. In decision support systems, the design of the queries is also very important, as you will see in Part IV of this book, Tuning SQL. Physical Data LayoutThis section looks at how the data on a DSS system should be configured. First, it looks at how to lay out the data on traditional disks; then it looks at disk arrays. I recommend using disk arrays if at all possible; the ease of use and performance benefits are worth the cost of the array. The main goal in designing the physical data layout is to balance the I/O across all the disks that are randomly accessed and to isolate the sequential I/O. Because the DSS system does not log very much, there isnt much sequential I/O to worry about. Because there is an insignificant amount of updating, the redo log files and archive logs do not see much use. You also know that the majority (if not all) of the data files are accessed in a random fashion but can take advantage of multiblock reads. Many of the concepts presented here represent the best-case scenario. Budgetary constraints may not allow everyone to buy the optimal number of disks for their DSS configuration. Make the best of these guidelines. Remember that it is the number of disks that provide performance with random I/Os; if you have the opportunity to buy one 4 gigabyte disk drive or two 2 gigabyte disk drives, the best performance comes from the two 2 gigabyte disk drives. Traditional Disks The layout for a typical DSS system is fairly straightforward. The minimal configuration should look something like this:
Both the data files and the indexes should be striped over as many disk drives as necessary to achieve optimal I/O rates on those disks. From Chapter 14, Advanced Disk I/O Concepts, remember that you can only push a disk drive to a maximum random I/O rate. As you have seen in previous chapters, the data and indexes can be striped across the disks using Oracle or RAID striping or a combination of the two. With OLTP and batch processing systems, I recommended OS or hardware striping (as is the case here). But unlike OLTP or batch processing systems, in order to accommodate the Oracle Parallel Query option, it is important that queries are more optimally divided if you have several large extents. If you do not use Oracle striping and build one large extent, you may not see the full benefits of the Parallel Query option. So I recommend using OS or hardware striping, but divide your tablespace into multiple data files, each with several large extents, to accommodate parallel query processing. I prefer a hardware disk array to manual Oracle striping primarily because the disk array provides excellent performance and is easy to use. When you use a disk array, the task of distributing I/Os can be greatly simplified.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |